from flask import Blueprint, render_template, request, redirect, url_for
import os
import sys
from werkzeug.utils import secure_filename
from controllers.index import index_page
from common.libs.Helper import ops_renderJSON
import pandas as pd
from application import ExoskeletonEngine as engine
from sqlalchemy import create_engine

upload_page = Blueprint("upload_page", __name__)


@upload_page.route('/bpm')
def indexbpm():
    return render_template('upload/bpm_upload.html')


@upload_page.route('/successbpm', methods=['POST'])
def successbpm():
    if request.method == 'POST':
        f = request.files['file']
        basepath = os.path.dirname(sys.path[0])  # 当前文件所在路径
        upload_path = os.path.join(basepath, '/www/Exoskeleton/static/uploads',
                                   secure_filename("bpm.xlsx"))  # 注意：没有的文件夹一定要先创建，不然会提示没有该路径
        f.save(upload_path)

        return render_template('upload/bpm_success.html', name=f.filename)


@upload_page.route('/api_upload_input_bpm', methods=['POST', 'GET'])
def inputdatabpm():
    ordata = pd.read_excel(r"/www/Exoskeleton/static/uploads/bpm.xlsx", header=2,
                           dtype={"序号": str, "公司": str, "审批月份": str, "部门": str, "姓名": str, "OA账户": str, "审批次数": int,
                                  "个人平均审批效率(小时)": float, "待办单据量": int, "平均待办等待时长(小时)": float}).set_index(
        '序号').reset_index()
    ordata = ordata[:-1]
    data = ordata.loc[:, ["公司", "部门", "OA账户", "姓名", "审批月份", "个人平均审批效率(小时)", "审批次数"]]

    data.rename(
        columns={'公司': 'company', '审批月份': 'date', '个人平均审批效率(小时)': 'timenum', '审批次数': 'totalnum', 'OA账户': 'oa',
                 '姓名': 'name', "部门": 'department'}, inplace=True)
    data.to_sql('bpm_data', engine, index=False, if_exists='append')

    return ops_renderJSON(msg="数据同步成功,载入数据模型成功")


@upload_page.route('/check')
def indexcheck():
    return render_template('upload/check_upload.html')


@upload_page.route('/successcheck', methods=['POST'])
def successcheck():
    if request.method == 'POST':
        f = request.files['file']
        basepath = os.path.dirname(sys.path[0])  # 当前文件所在路径
        upload_path = os.path.join(basepath, '/www/Exoskeleton/static/uploads',
                                   secure_filename("check.xlsx"))  # 注意：没有的文件夹一定要先创建，不然会提示没有该路径
        f.save(upload_path)

        return render_template('upload/check_success.html', name=f.filename)


@upload_page.route('/api_upload_input_check', methods=['POST', 'GET'])
def inputdatacheck():
    req = request.values
    yeardata = req['yeardata'] if "yeardata" in req else "666"
    monthdata = req['monthdata'] if "monthdata" in req else "666"

    data = pd.read_excel(r"/www/Exoskeleton/static/uploads/check.xlsx", header=3,
                         dtype={"公司": str, "一级部门": str, "员工组": str, "员工姓名": str, "员工编号": int, "排班天数": float,
                                "事假合计天数": float, "年假天数": float, "迟到人数": float, "早退天数": float, "休假天数": float,
                                "日均加班时长": float})

    data = data.drop(["序号"], axis=1)
    data = data.loc[data["员工组"] == "编内"]
    data = data.fillna(0)
    data["迟到人数"] = data["迟到1次数"] + data["迟到2次数"]
    data["休假天数"] = data["事假合计天数"] + data["病假合计天数"] + data["婚假天数"] + data["丧假天数"] + data["陪产假天数"] + data[
        "工伤假天数"]

    data = data.loc[:, [
                           "公司",
                           "一级部门",
                           "员工组",
                           "员工姓名",
                           "员工编号",
                           "排班天数",
                           "事假合计天数",
                           "年假天数",
                           "旷工合计天数",
                           "迟到人数",
                           "早退次数",
                           "休假天数",
                           "日均加班时长"
                       ]]
    data.rename(columns={

        "公司": "company",
        "一级部门": "dapartment",
        "员工组": "group",
        "员工姓名": "name",
        "员工编号": "number",
        "排班天数": "days",
        "事假合计天数": "vacation",
        "年假天数": "annualvacation",
        "旷工合计天数": "absent",
        "迟到人数": "late",
        "早退次数": "leave",
        "休假天数": "sumvacation",
        "日均加班时长": "overtime"
    }, inplace=True)

    data["month"] = int(monthdata)
    data["years"] = int(yeardata)
    num = (int(yeardata) - 2020) * 12 + int(monthdata)
    numlist = [num - 1, num - 2]
    data["num"] = num

    db = create_engine('mysql+pymysql://root:116648728g@localhost:3306/exoskeleton')
    olddata = pd.read_sql('select * from check_data', db)
    olddata = olddata.loc[(olddata["num"].isin(numlist))]

    newnumber = data["number"].tolist()
    oldnumber = olddata["number"].tolist()
    dif = list(set(oldnumber).difference(set(newnumber)))
    dimisdata = olddata.loc[(olddata["number"].isin(dif))]
    dimisdata["mark"] = 1

    othernumber = list(set(oldnumber).difference(set(dif)))
    print(othernumber)
    undimisdata = olddata.loc[(olddata["number"].isin(othernumber))]
    undimisdata["mark"] = 0

    aidata = undimisdata.append(dimisdata)
    aidata = aidata.loc[:, [
                               "name",
                               "number",
                               "annualvacation",
                               "vacation",
                               "late",
                               "leave",
                               "overtime",
                               "mark"
                           ]]

    data.to_sql('check_data', db, index=True, if_exists='append')
    aidata.to_sql('ai_checkdata', db, index=True, if_exists='append')

    return ops_renderJSON(msg="数据同步成功,载入数据模型成功")



# @index_page.route('/api_upload_input', methods=['POST', 'GET'])
# def inputdata1():
#     if request.method == 'POST':
#         req = request.values
#         yeardata = req['yeardata'] if "yeardata" in req else "666"
#         monthdata = req['monthdata'] if "monthdata" in req else "666"
#
#
#         data = pd.read_excel(r"D:\Project\Python\Exoskeleton\uploadfile\uploadfile.xls", header=3,
#                              dtype={"公司": str, "一级部门": str, "员工组": str, "员工姓名": str, "员工编号": int, "排班天数": float,
#                                     "事假合计天数": float, "年假天数": float, "迟到人数": float, "早退天数": float, "休假天数": float,
#                                     "日均加班时长": float})
#
#         data = data.drop(["序号"], axis=1)
#         data = data.loc[data["员工组"] == "编内"]
#         data = data.fillna(0)
#         data["迟到人数"] = data["迟到1次数"] + data["迟到2次数"]
#         data["休假天数"] = data["事假合计天数"] + data["病假合计天数"] + data["婚假天数"] + data["丧假天数"] + data["陪产假天数"] + data[
#             "工伤假天数"]
#
#         data = data.loc[:, [
#                                "公司",
#                                "一级部门",
#                                "员工组",
#                                "员工姓名",
#                                "员工编号",
#                                "排班天数",
#                                "事假合计天数",
#                                "年假天数",
#                                "旷工合计天数",
#                                "迟到人数",
#                                "早退次数",
#                                "休假天数",
#                                "日均加班时长"
#                            ]]
#         data.rename(columns={
#
#             "公司": "company",
#             "一级部门": "dapartment",
#             "员工组": "group",
#             "员工姓名": "name",
#             "员工编号": "number",
#             "排班天数": "days",
#             "事假合计天数": "vacation",
#             "年假天数": "annualvacation",
#             "旷工合计天数": "absent",
#             "迟到人数": "late",
#             "早退次数": "leave",
#             "休假天数": "sumvacation",
#             "日均加班时长": "overtime"
#         }, inplace=True)
#
#
#
#         data["month"] = int(monthdata)
#         data["years"] = int(yeardata)
#         num = (int(yeardata) - 2020) * 12 + int(monthdata)
#         numlist = [num - 1, num - 2]
#         data["num"]=num
#
#         db = create_engine('mysql+pymysql://root:116648728g@localhost:3306/exoskeleton')
#         olddata = pd.read_sql('select * from checkdata', db)
#         olddata = olddata.loc[(olddata["num"].isin(numlist))]
#
#         newnumber = data["number"].tolist()
#         oldnumber = olddata["number"].tolist()
#         dif = list(set(oldnumber).difference(set(newnumber)))
#         dimisdata = olddata.loc[(olddata["number"].isin(dif))]
#         dimisdata["mark"] = 1
#
#         othernumber = list(set(oldnumber).difference(set(dif)))
#         print(othernumber)
#         undimisdata = olddata.loc[(olddata["number"].isin(othernumber))]
#         undimisdata["mark"] = 0
#
#         aidata = undimisdata.append(dimisdata)
#         aidata = aidata.loc[:, [
#                                    "name",
#                                    "number",
#                                    "annualvacation",
#                                    "vacation",
#                                    "late",
#                                    "leave",
#                                    "overtime",
#                                    "mark"
#                                ]]
#
#         data.to_sql('checkdata', db, index=True, if_exists='append')
#         aidata.to_sql('ai_checkdata', db, index=True, if_exists='append')
#
#     return ops_renderJSON(msg="数据同步成功,载入数据模型成功")
